package jdbc.hr.dao;

import java.sql.Connection;
import java.sql.Date;

import java.sql.PreparedStatement;
import java.sql.ResultSet;import java.sql.SQLException;
import java.util.ArrayList;

import jdbc.hr.domain.Department;
import jdbc.hr.domain.Employee;
import jdbc.hr.util.DatabaseManager;

//Employee(직원) table과 연관된 DB작업(CRUD)를 처리하는 DAO클래스
//싱글톤으로 작업한 것과 비교해봐라
//일단 자주쓰는 클로즈 메소드 부터 편하게 만들자. 
public class EmployeeDAO {
	
	private static EmployeeDAO instance = null;
	
	private DatabaseManager dm;

	private EmployeeDAO(){
		dm = DatabaseManager.getInstance();
	}
	
	public static EmployeeDAO getInstance(){
		if(instance == null){
			instance = new EmployeeDAO();
		}
		return instance;
	}

	/**
	 * 직원 정보(Employee객체)를 매개변수로 받아 employee 테이블에 insert 하는 메소드
	 * return type : insert된 레코드 row의 수
	 */
	
	
	public int insertEmployee(Employee emp) throws SQLException{
		String sql = "insert into emplotee values(?,?,?,?,?,?,?)";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = -1;
		
		try{			
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			  
			pstmt.setString(1, emp.getEmployeeId());
			pstmt.setString(2, emp.getEmployeeName());
			pstmt.setString(3, emp.getEmail());
			pstmt.setString(4, emp.getJobPosition());
			
			long i = emp.getHireDate().getTime();
			//이게 뭐지, 
			java.sql.Date dd = new Date(i);
			
			pstmt.setDate(6, dd);
			//한줄로 쓰면 다음과 같다.
			//pstmt.setDate(6, new(Date(emp.getHireDate().getTime())));
			
			//만약 이렇게 쓰려면 문제가 생긴다. setDate와 emp.getHireDate()은 데이터 타입이 맞지 않는다. 
			//pstmt.setDate(6, emp.getHireDate());
			//이걸 api를 확인해서 타입을 확인해서 왜 못들어가는지 공부하자. 
			pstmt.setInt(5, emp.getSalary());
			pstmt.setString(7, emp.getDepartmentID());
			
			count = pstmt.executeUpdate();
			return count;
		}finally{
			dm.close(conn, pstmt);
		}
		
		
	}
	/*
	 * 매개변수로 받은 직원정보로 디비에 저장된 정보를 수정하는 메소드
	 * 매개변수로 받은 직원정보의 employee_id와 동일한 row의 나머지 정보를 수정
	 */
	public int updateEmployee(Employee emp) throws SQLException{
		String sql = "update emplotee set employee_name = ?, email = ?, job_position=?, salary = ?, hire_date =?, department_id =?  where employee_id = ?";
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	try{
		conn = dm.getConnection();
		pstmt = conn.prepareStatement(sql);
		
		pstmt.setString(7, emp.getEmployeeId());
		pstmt.setString(1, emp.getEmployeeName());
		pstmt.setString(2, emp.getEmail());
		pstmt.setString(3, emp.getJobPosition());
		pstmt.setInt(4, emp.getSalary());
		
		long i = emp.getHireDate().getTime();
		java.sql.Date dd = new Date(i);
		pstmt.setDate(5, dd);
		
		pstmt.setString(6, emp.getDepartmentID());
		
		return pstmt.executeUpdate();
		
	}finally{
		dm.close(conn, pstmt);
	}
	
	}
	
	/*
	 * 매개 변수로 받은 직원_ID의 직원 데이터를 삭제하는 메소드
	 */
	public int deleteEmployeeById(String employeeId)throws SQLException{
		
		String sql = "delete from emplotee where employee_id = ?";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try{
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, employeeId);
			
			return pstmt.executeUpdate();
			
		}finally{
			dm.close(conn, pstmt);
		} 
		
		
	}
	
	//매개변수로 받은 부서_id의 직원들을 삭제하는 메소드
	public int deleteEmployeeByDepartmentId(String department) throws SQLException{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = null;
		if(department != null){
			sql = "delete from emplotee where department_id = ?";
		}else{
			sql = "delete from emplotee where department_id is null";
			//sql문에서 null값을 조회하려면 = null이 아니고, is null로 조회한다.
		}
		
		try{
		conn = dm.getConnection();
		pstmt = conn.prepareStatement(sql);
		
		pstmt.setString(1, department);
		
		return pstmt.executeUpdate();
	
		}finally{
			dm.close(conn, pstmt);
			
		}
		
	}
	
//	조회 - select : 1개 row, n개 row
// 직원_id로 직원정보 조회하는 메소드 -1개 row조회
	public Employee selectEmployeebyId(String employeeId)throws SQLException{
		String sql = "select * from emplotee where employee_id = ?";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try{
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, employeeId);
			
			rset = pstmt.executeQuery();
			rset.next();
			return new Employee(rset.getString(1), rset.getString(2),rset.getString(3),rset.getString(4),rset.getDate(6),rset.getInt(5),rset.getString(7));
			
		}finally{
			dm.close(conn, pstmt, rset);	
		}
		
	}
	
	//총 직원수를 조회하는 메소드
	//count()
	public int selectTotalEmploteeCount() throws SQLException{
		
		String sql = "select count(employee_id) from emplotee";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try{
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			rset = pstmt.executeQuery();
			rset.next();
			
			return rset.getInt(1);
			
			
			
		}finally{
			dm.close(conn, pstmt, rset);
			
		}
	}
	
	
	public Employee selectEmployeeByIdJoinDepartment(String employeeId) throws SQLException{
		
		String sql ="select e.employee_id, e.employee_name,e.email,e.job_position,e.salary,e.hire_date, d.Department_id,d.department_name,d.location from emplotee e, department d where e.employee_id= ? and e.department_id = d.department_id(+)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try{
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, employeeId);
			
			Employee emp = null;
			rset = pstmt.executeQuery();
			if(rset.next()){
				Department dept = null;
				if(rset.getString(7)!= null){//부서가 있는 직원
					dept = new Department(rset.getString(7),rset.getString(8),rset.getString(9));

				}
				emp = new Employee(rset.getString(1),rset.getString(2),rset.getString(3),rset.getString(4),rset.getDate(6),rset.getInt(5),rset.getString(7),dept);
				
				}
			
			return emp;
			
		}finally{
			dm.close(conn, pstmt, rset);
			
		}
		
	}
	//전체 직원 정보 조회 - 조회row수 : N개
	public ArrayList<Employee> selectEmployeeList()throws SQLException{
		ArrayList<Employee> list = new ArrayList<>();
		
		//조인 테이블이 들어가니까 이렇게 쓰면 안되징
		//String sql = "select * from emplotee";
		String sql = "select e.employee_id, e.employee_name,e.email,e.job_position,e.salary,e.hire_date, d.Department_id,d.department_name,d.location from emplotee e, department d where e.department_id = d.department_id(+)";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try{
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			rset = pstmt.executeQuery();
			
				Department dept = null;
				while(rset.next()){
					if(rset.getString(7)!= null){//부서가 있는 직원
						dept = new Department(rset.getString(7),rset.getString(8),rset.getString(9));
					list.add(new Employee(rset.getString(1),rset.getString(2),rset.getString(3),rset.getString(4),rset.getDate(6),rset.getInt(5),rset.getString(7),dept));
					}
			
				}
		}finally{
			dm.close(conn, pstmt, rset);
			
		}
		return list;

	}
	
	
	//연봉의 범위로 직원 정보 조회 - 조회 row 수 : n개
	public ArrayList<Employee> selectEmployeeBySalaryRange(int sSalary, int eSalary) throws SQLException{
		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select e.employee_id, e.employee_name,e.email,e.job_position,e.salary,e.hire_date, d.Department_id,d.department_name,d.location from emplotee e, department d where e.salary between ? AND ? and e.department_id = d.department_id(+)";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null ;
		
		try{
			conn = dm.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, sSalary);
			pstmt.setInt(2, eSalary);
			rset = pstmt.executeQuery();
			
			Department dept = null;
			
			while(rset.next()){
				if(rset.getString(7)!= null){//부서가 있는 직원
					dept = new Department(rset.getString(7),rset.getString(8),rset.getString(9));
				list.add(new Employee(rset.getString(1),rset.getString(2),rset.getString(3),rset.getString(4),rset.getDate(6),rset.getInt(5),rset.getString(7),dept));
				}
			}
				
		}finally{
			dm.close(conn, pstmt, rset);
		
		}
	
		return list;

	}
	
	
	
}
